# 导入库
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import os
from sqlalchemy import create_engine
from matplotlib.font_manager import FontProperties

# 中文显示配置
plt.rcParams["font.family"] = ["SimHei", "SimSun", "PingFang SC", "Microsoft YaHei"]
plt.rcParams["axes.unicode_minus"] = False
plt.rcParams["font.size"] = 10

# 数据库配置（脱敏）
db_config = {
    "host": "【数据库地址占位符】",
    "user": "【数据库用户名占位符】",
    "password": "******",
    "database": "lendingclub_loan_db_v1",
    "port": 3306,
    "table_name": "loan_2018_q1"
}

# 图表保存路径（占位符）
save_dir = "【图表保存路径占位符】"
if not os.path.exists(save_dir):
    os.makedirs(save_dir)

# 数据读取和预处理函数（复用核心逻辑）
def load_data(engine, table_name):
    needed_fields = [
        "installment", "emp_length", "home_ownership", "annual_inc", 
        "loan_status", "dti", "delinq_2yrs", "fico_range_low", 
        "inq_last_6mths", "open_acc", "revol_util"
    ]
    try:
        df = pd.read_sql_table(table_name=table_name, con=engine, columns=needed_fields)
        return df
    except Exception as e:
        print(f"读取失败：{str(e)}")
        exit()

def preprocess_data(df):
    # 异常值过滤
    df_clean = df[
        (df["annual_inc"] > 0) & (df["annual_inc"] <= 1000000)  
        & (df["fico_range_low"].between(300, 850))
        & (df["dti"] >= 0) & (df["dti"] <= 100) 
        & (df["revol_util"].between(0, 100))
        & (df["open_acc"].between(0, 20))
        & (df["home_ownership"].isin(["RENT", "MORTGAGE", "OWN"]))  
    ].copy()

    # 缺失值处理
    df_clean.dropna(inplace=True)

    # 新特征计算
    df_clean["monthly_pressure"] = df_clean["installment"] / (df_clean["annual_inc"] / 12)
    df_clean = df_clean[df_clean["monthly_pressure"] <= 1]

    # 字段分箱
    df_clean["pressure_bin"] = pd.cut(
        df_clean["monthly_pressure"],
        bins=[0, 0.1, 0.25, 0.3, 1],
        labels=["低(0-10%)", "较低(10-25%)", "中(25-30%)", "高(30%+)"],
        right=False
    )

    df_clean["dti_bin"] = pd.cut(
        df_clean["dti"],
        bins=[0, 10, 20, 36, float("inf")],
        labels=["低(0-10%)", "中(10-20%)", "较高(20-36%)", "高(36%+)"],
        right=False
    )

    df_clean["fico_bin"] = pd.cut(
        df_clean["fico_range_low"],
        bins=[660, 700, 750, 850], 
        labels=["一般(660-699)", "良好(700-749)", "优质(750-850)"],
        right=False 
    )

    df_clean["delinq_bin"] = pd.cut(
        df_clean["delinq_2yrs"],
        bins=[-1, 0, 2, float("inf")],
        labels=["无(0次)", "少(1-2次)", "多(3次+)"],
        right=True
    )

    df_clean["inq_bin"] = pd.cut(
        df_clean["inq_last_6mths"],
        bins=[-1, 0, 1, 2, float("inf")],
        labels=["0次", "1次", "2次", "3次+"],
        right=True
    )

    df_clean["emp_bin"] = pd.cut(
        df_clean["emp_length"],
        bins=[0, 1, 3, 10, 40],
        labels=["不足1年", "1-2年", "3-9年", "10年及以上"],
        right=False
    )

    df_clean["open_bin"] = pd.cut(
        df_clean["open_acc"], 
        bins=[0, 5, 9, 13, float("inf")],
        labels=["账户数0-4", "账户数5-8", "账户数9-12", "账户数12+"], 
        right=False
    )

    df_clean["revol_bin"] = pd.cut(
        df_clean["revol_util"],
        bins=[0, 10, 60, 80, float("inf")],
        labels=["低(0-10%)", "中(10-60%)", "较高(60-80%)", "高(80%+)"],
        right=False
    )

    return df_clean

# 交叉分析函数
def cross_analysis(df, save_dir):
    print("开始特征交叉分析")

    # 组合1：dti × fico分数
    cross1 = pd.crosstab(
        index=df["dti_bin"], 
        columns=df["fico_bin"], 
        values=df["loan_status"], 
        aggfunc="mean"
    ).round(4)
    cross1_pct = (cross1 * 100).round(2)
    print("\ndti×fico分数 逾期率（%）：")
    print(cross1_pct)

    # 绘制热力图
    plt.figure(figsize=(10, 6))
    heatmap = sns.heatmap(
        cross1_pct, 
        annot=True, 
        cmap="YlOrRd", 
        fmt=".2f", 
        cbar_kws={"label": "逾期率(%)"}
    )
    plt.title("dti×fico分数 逾期率热力图", fontsize=14, pad=20)
    plt.xlabel("fico信用分")
    plt.ylabel("债务收入比")
    plt.tight_layout()
    fig_path = f"{save_dir}\\组合1_dti_fico逾期率热力图.png"
    plt.savefig(fig_path, dpi=300, bbox_inches="tight")
    plt.close()

    # 组合2：还款压力 × 逾期次数
    cross2 = pd.crosstab(
        index=df["pressure_bin"], 
        columns=df["delinq_bin"], 
        values=df["loan_status"], 
        aggfunc="mean"
    ).round(4)
    cross2_pct = (cross2 * 100).round(2)
    print("\n还款压力×逾期次数 逾期率（%）：")
    print(cross2_pct)

    plt.figure(figsize=(10, 6))
    heatmap = sns.heatmap(
        cross2_pct, 
        annot=True, 
        cmap="YlOrRd", 
        fmt=".2f", 
        cbar_kws={"label": "逾期率(%)"}
    )
    plt.title("还款压力×逾期次数 逾期率热力图", fontsize=14, pad=20)
    plt.xlabel("近两年逾期次数")
    plt.ylabel("月还款压力")
    plt.tight_layout()
    fig_path = f"{save_dir}\\组合2_还款压力_逾期次数逾期率热力图.png"
    plt.savefig(fig_path, dpi=300, bbox_inches="tight")
    plt.close()

    # 组合3：还款压力 × 查询次数
    cross3 = pd.crosstab(
        index=df["pressure_bin"], 
        columns=df["inq_bin"], 
        values=df["loan_status"], 
        aggfunc="mean"
    ).round(4)
    cross3_pct = (cross3 * 100).round(2)
    print("\n还款压力×查询次数 逾期率（%）：")
    print(cross3_pct)

    plt.figure(figsize=(10, 6))
    ax = cross3_pct.plot(
        kind="bar", 
        color=["#2ECC71", "#F39C12", "#E67E22", "#E74C3C"],
        rot=0
    )
    plt.title("还款压力×查询次数 逾期率对比", fontsize=14, pad=20)
    plt.xlabel("月还款压力")
    plt.ylabel("逾期率(%)")
    plt.legend(title="近6个月征信查询次数", bbox_to_anchor=(1.05, 1), loc="upper left")
    plt.grid(axis="y", alpha=0.3)

    # 添加数值标签
    for container in ax.containers:
        ax.bar_label(container, fmt="%.2f", fontsize=8)

    plt.tight_layout()
    fig_path = f"{save_dir}\\组合3_还款压力_查询次数逾期率条形图.png"
    plt.savefig(fig_path, dpi=300, bbox_inches="tight")
    plt.close()

    # 组合4：三维交叉（住房状态×还款压力×工作年限）
    df_3d_4 = df[["home_ownership", "pressure_bin", "emp_bin", "loan_status"]].copy()
    df_3d_4 = df_3d_4.dropna()
    df_3d_4["home_ownership"] = df_3d_4["home_ownership"].replace({
        "RENT": "租房", "MORTGAGE": "按揭房", "OWN": "自有房"
    })

    emp_groups = ["不足1年", "1-2年", "3-9年", "10年及以上"]
    fig, axes = plt.subplots(1, len(emp_groups), figsize=(5*len(emp_groups), 6), sharey=False)
    fig.suptitle("三维交叉：住房状态×还款压力×工作年限 逾期率热力图", fontsize=14, y=1.02)

    for i, emp in enumerate(emp_groups):
        df_sub = df_3d_4[df_3d_4["emp_bin"] == emp].copy()
        if df_sub.empty:
            axes[i].set_title(f"工作年限：{emp}\n（无数据）")
            axes[i].axis("off")
            continue

        cross_3d = pd.crosstab(
            index=df_sub["home_ownership"],
            columns=df_sub["pressure_bin"],
            values=df_sub["loan_status"],
            aggfunc="mean"
        ).round(4)
        cross_3d_pct = (cross_3d * 100).round(2)

        heatmap = sns.heatmap(
            cross_3d_pct,
            ax=axes[i],
            annot=True,
            cmap="YlOrRd",
            fmt=".2f",
            cbar=(i == len(emp_groups)-1)
        )

        if i == 0:
            axes[i].set_yticklabels(cross_3d.index, fontsize=9)
            axes[i].set_ylabel("住房状态")
        else:
            axes[i].set_yticklabels([])
            axes[i].set_ylabel("")

        axes[i].set_title(f"工作年限：{emp}", fontsize=10)
        axes[i].set_xlabel("还款压力")
        axes[i].set_xticklabels(axes[i].get_xticklabels(), rotation=45, ha="right")

    plt.tight_layout()
    fig_path = f"{save_dir}\\组合4_三维_住房状态_还款压力_工作年限.png"
    plt.savefig(fig_path, dpi=300, bbox_inches="tight")
    plt.close()

    # 组合5：三维交叉（账户数×利用率×dti）
    df_3d_5 = df[["open_bin", "revol_bin", "dti_bin", "loan_status"]].copy()
    df_3d_5 = df_3d_5.dropna()

    dti_groups = ["低(0-10%)", "中(10-20%)", "较高(20-36%)", "高(36%+)"]
    fig, axes = plt.subplots(1, len(dti_groups), figsize=(5*len(dti_groups), 6), sharey=False)
    fig.suptitle("三维交叉：未结清账户数×循环信用利用率×dti 逾期率热力图", fontsize=16, y=1.02)

    for i, dti in enumerate(dti_groups):
        df_sub = df_3d_5[df_3d_5["dti_bin"] == dti].copy()
        if df_sub.empty:
            axes[i].set_title(f"dti：{dti}\n（无数据）")
            axes[i].axis("off")
            continue

        cross_3d = pd.crosstab(
            index=df_sub["open_bin"],
            columns=df_sub["revol_bin"],
            values=df_sub["loan_status"],
            aggfunc="mean"
        ).round(4)
        cross_3d_pct = (cross_3d * 100).round(2)

        heatmap = sns.heatmap(
            cross_3d_pct,
            ax=axes[i],
            annot=True,
            cmap="YlOrRd",
            fmt=".2f",
            cbar=(i == len(dti_groups)-1)
        )

        if i == 0:
            axes[i].set_yticklabels(cross_3d.index, fontsize=9)
            axes[i].set_ylabel("未结清账户数")
        else:
            axes[i].set_yticklabels([])
            axes[i].set_ylabel("")

        axes[i].set_title(f"dti：{dti}", fontsize=11)
        axes[i].set_xlabel("循环信用利用率")
        axes[i].set_xticklabels(axes[i].get_xticklabels(), rotation=45, ha="right")

    plt.tight_layout()
    fig_path = f"{save_dir}\\组合5_三维_账户数_账户利用率_dti.png"
    plt.savefig(fig_path, dpi=300, bbox_inches="tight")
    plt.close()

# 主执行逻辑
if __name__ == "__main__":
    try:
        # 建立数据库连接
        engine = create_engine(
            f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}"
        )

        # 执行流程
        df_raw = load_data(engine, db_config["table_name"])
        df_clean = preprocess_data(df_raw)
        cross_analysis(df_clean, save_dir)

        print(f"\n所有交叉分析完成！图表已保存到：{save_dir}")
    except Exception as e:
        print(f"程序执行出错：{str(e)}")

